package com.yzzrjy.Dao.Impl;

import com.yzzrjy.Dao.Interfaces.BlogDao;
import com.yzzrjy.bean.BlogCommentBean;
import com.yzzrjy.bean.BlogInfoBean;
import com.yzzrjy.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

public class BlogDaoMsql implements BlogDao {

    @Override
    public void addBlog(String uid, String text, String content, byte type, String city) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("insert into blog_list(uid,type,text,content,city,add_time) values(?,?,?,?,?,now())",
                uid, type,text, content, city);
    }

    @Override
    public void addBlog(long newID, String uid, String text, String content, byte type, String city) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("insert into blog_list(id,uid,type,text,content,city,add_time) values(?,?,?,?,?,?,now())", newID, uid, type,text, content, city);
    }

    //测试用
    @Override
    public List<BlogInfoBean> getBlogList(String myUid, String sex, int start, int showPageSize) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*,b.face,b.nickname,b.birthday,b.sex," +
                "exists(select blog_id from blog_likes where blog_id=a.id and liked_uid=?) AS liked," +
                "(select count(*) from blog_likes where blog_id=a.id) AS like_count," +
                "(SELECT COUNT(*) from blog_comments where blog_id=a.id) as comment_count " +
                "from blog_list a, user_info b where a.uid=b.uid and b.sex=? order by a.id desc limit ?,?";
        List<BlogInfoBean> list = queryRunner.query(sql, new BeanListHandler<>(BlogInfoBean.class), myUid, sex, start, showPageSize);
        return list;
    }

    //正式用
    @Override
    public List<BlogInfoBean> getBlogList(String myUid, String blogIds) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*,b.face,b.nickname,b.birthday,b.sex," +
                "exists(select blog_id from blog_likes where blog_id=a.id and liked_uid=?) AS liked," +
                "(select count(*) from blog_likes where blog_id=a.id) AS like_count," +
                "(SELECT COUNT(*) from blog_comments where blog_id=a.id) as comment_count " +
                "from blog_list a, user_info b where a.uid=b.uid and a.id in(" + blogIds + ")";
        List<BlogInfoBean> list = queryRunner.query(sql, new BeanListHandler<>(BlogInfoBean.class), myUid);
        return list;
    }

    @Override
    public void updateBlogLiked(String blogId, String likedUid, boolean liked) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        if(liked){
            queryRunner.update("insert into blog_likes values (null,?,?)", blogId, likedUid);
        }else {
            queryRunner.update("delete from blog_likes where blog_id=? and liked_uid=?", blogId, likedUid);
        }
    }

    @Override
    public void addComment(String blogId, String uid, String parentUid, String content) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        queryRunner.update("insert into blog_comments(blog_id,comment_uid,parent_uid,content,time) values (?,?,?,?,now())",
                blogId, uid, parentUid, content);
    }

    @Override
    public List<BlogCommentBean> getBlogComment(String blogId) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        String sql = "select a.*,b.face,b.nickname," +
                "(select nickname from user_info where uid=a.parent_uid) AS parent_nickname " +
                "from blog_comments a, user_info b where a.comment_uid=b.uid and a.blog_id=? order by a.id desc";
        List<BlogCommentBean> query = queryRunner.query(sql, new BeanListHandler<>(BlogCommentBean.class), blogId);
        return query;
    }
}
